from mydb.odbc import atable

#odbc=base()
#odbc.dsn='payroll'
#odbc.charset='cp866'
#gen=odbc.generate('SELECT * FROM Anketa')
#odbc.commit('CREATE TABLE E:\Py\MyPy\progs\payroll\test.dbf (test varchar(10))')


m06_err=atable()
m06_err.table.base.dsn='cen'
m06_err.name='m06_err'

def classes_pc():
	"This function returns parent-child tree of classes"
	classes=atable()
	classes.table.base.dsn='alldata'
	classes.name='classes'
	classes_sel=classes.select(['ALLTRIM(code)', 'ALLTRIM(name)'])
	classes_codes=['00']
	for item in classes_sel:
		classes_codes+=[item[0].strip().strip('_')]
	result=[]
	for item in classes_codes:
		item_len=len(item)
		if item=='00':
			result+=[['',item]]
		else:
			test=True
			for i in range(item_len-2, 0, -1):
				if item[:item_len-i] in classes_codes:
					result+=[[item[:item_len-1], item]]
					test=False
					break
			if test:
				result+=[[classes_codes[0], item]]
	
	return result
		
def classes_dict():
	"This function returns name-code and code-name dictionary of classes"
	classes=atable()
	classes.table.base.dsn='alldata'
	classes.name='classes'
	classes_sel=classes.select(['ALLTRIM(code)', 'ALLTRIM(name)'])
	result={}
	result['codes']={}
	result['names']={}
	result['codes']['00']='Все'
	result['names']['Все']='00'
	for item in classes_sel:
		result['codes'][item[0].strip().strip('_')]=item[1].strip().strip('_')
		result['names'][item[1].strip().strip('_')]=item[0].strip().strip('_')
		
	return result

#def struct10():
#	"This function returns structure for 10"
#	mat_obr=atable()
#	mat_obr.table.base.dsn='cen'
#	#nom_g=mat_obr.table.base.generate("SELECT DISTINCT a.nom_nom FROM mat_obr a, mat_obr b WHERE a.nom_nom=b.nom_nom AND a.vo='32' AND b.vo='52' AND a.kol_d=-1*b.kol_d AND a.cen_zav<>b.cen_zav AND a.ceh=b.sklad AND b.ceh=a.sklad")
#	saldo_n==mat_obr.table.base.generate("""SELECT a.bal_ch, a.sklad, b.naimuch, SUBSTR(a.nom_nom,1,2) AS "group", '' AS "mol", kol_d AS "saldo_n_kol", sum_d AS "saldo_n_sum" FROM mat_obr a, b.s_uch WHERE a.vo=' 1' GROUP BY a.bal_ch, a.sklad, SUBSTR(a.nom_nom,1,2)""")
#	prihod=mat_obr.table.base.generate("""SELECT a.bal_ch, a.sklad, b.naimuch, SUBSTR(a.nom_nom,1,2) AS "group", '' AS "mol", kol_d, sum_d FROM mat_obr a, b.s_uch WHERE vo='32' OR vo='30' GROUP BY a.bal_ch, a.sklad, SUBSTR(a.nom_nom,1,2)""")
#	rashod=mat_obr.table.base.generate("""SELECT a.bal_ch, a.sklad, b.naimuch, SUBSTR(a.nom_nom,1,2) AS "group", '' AS "mol", -1*kol_d, -1*sum_d FROM mat_obr a, b.s_uch WHERE vo='52' OR vo='51' OR vo='50' GROUP BY a.bal_ch, a.sklad, SUBSTR(a.nom_nom,1,2)""")
#	correct=mat_obr.table.base.generate("""SELECT a.bal_ch, a.sklad, b.naimuch, SUBSTR(a.nom_nom,1,2) AS "group", '' AS "mol", -1*kol_d, -1*sum_d FROM mat_obr a, b.s_uch WHERE vo=' 2' GROUP BY a.bal_ch, a.sklad, SUBSTR(a.nom_nom,1,2)""")
#	saldo_k=mat_obr.table.base.generate("""SELECT a.bal_ch, a.sklad, b.naimuch, SUBSTR(a.nom_nom,1,2) AS "group", '' AS "mol", -1*kol_d, -1*sum_d FROM mat_obr a, b.s_uch WHERE vo='52' OR vo='51' OR vo=' 3' OR vo=' 4' OR vo=' 5' OR vo=' 6' OR vo=' 7' OR vo=' 8' OR vo=' 9' GROUP BY a.bal_ch, a.sklad, SUBSTR(a.nom_nom,1,2)""")
	
#	dict=classes_dict()['codes']
	
	
	
#	nom_g=mat_obr.table.base.generate("SELECT DISTINCT a.nom_nom FROM mat_obr a, mat_obr b WHERE a.nom_nom=b.nom_nom AND a.vo='32' AND b.vo='52' AND a.ceh=b.sklad AND b.ceh=a.sklad")
#	nom_list=[]
#	for nom in nom_g:
#		nom_list+=[nom[0]]
	
#	err_list1=[]
#	for nom in nom_list:
#		err_g=mat_obr.table.base.generate("SELECT nom_nom, SUM(kol_d), SUM(sum_d) FROM mat_obr WHERE (vo='52' OR vo='32') AND nom_nom='"+nom+"' GROUP BY nom_nom HAVING SUM(kol_d)<>0 OR SUM(sum_d)<>0")
#		for err in err_g:
#			err_list1+=[err[0]]
	
#	err_list=[]
#	for nom in err_list1:
#		err_g=mat_obr.table.base.generate("SELECT blk_no, nom_nom, sklad, ceh, zak, ei, cen_zav, vo, kol, sum_zav, ost_ksu, sum_post, sum_chet, kol_tn, cen_post, kol_d, sum_d, kol_i, sum_i, n_ksu, ost_ksud FROM mat_obr WHERE nom_nom='"+nom+"'")
#		for err in err_g:
#			err_list+=[err]

#	return err_list